﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WinStudent
{
    public partial class FrmStudentList : Form
    {
        public FrmStudentList()
        {
            InitializeComponent();
        }

        //单例  只有一个实例
        //private static FrmStudentList frmStudentList = null;
        //public static FrmStudentList CreateInstance()
        //{
        //    if (frmStudentList==null||frmStudentList.IsDisposed)
        //    {
        //        frmStudentList = new FrmStudentList();
        //    }
        //    return frmStudentList;
        //}

        private void FrmStudentList_Load(object sender, EventArgs e)
        {
            InitStudentList();
            InitSdeptList();
        }

        /// <summary>
        /// 学生信息列表框
        /// </summary>
        private void InitStudentList()
        {
            string sql = "select Sno,Sname,Ssex,Sage,Sdept from Student where IsDeleted=0 ";
            DataTable dataTableStudentList = SqlHelper.GetDataTable(sql);
            dgvStudentList.DataSource = dataTableStudentList;
            Init_colEdit_colDelete(dgvStudentList);
        }

        /// <summary>
        /// 初始化所在院系下拉框
        /// </summary>
        private void InitSdeptList()
        {
            string sqlSdept = "select distinct Sdept from Student where Sdept is not null order by Sdept desc ";
            DataTable dataTableSdept = SqlHelper.GetDataTable(sqlSdept);
            //添加一行默认选择
            DataRow datarow = dataTableSdept.NewRow();
            datarow["Sdept"] = "任意院系";
            dataTableSdept.Rows.InsertAt(datarow,0);

            cboSdept.DataSource = dataTableSdept;
            //学号--项
            cboSdept.DisplayMember = "Sdept";//显示的内容
            cboSdept.ValueMember = "Sdept";//值

            cboSdept.SelectedIndex = 0;
        }

        private void btnFind_Click(object sender, EventArgs e)
        {
            //dgvStudentList.Columns.Remove("colEdit");
            //dgvStudentList.Columns.Remove("colDelete");
            //dgvStudentList.DataSource = null;
            string sdept = (string)cboSdept.SelectedValue;
            string sname = txtSname.Text.Trim();

            string sql = "select Sno,Sname,Ssex,Sage,Sdept from Student where IsDeleted=0 and Sname like @Sname ";

            //if (cboSdept.SelectedIndex != 0)
            //{
            //    sql += "and Sdept='" + sdept + "' ";
            //}
            //if (txtSname.Text.Trim() != null)
            //{
            //    sql += "and Sname like'%" + sname + "%' ";
            //}
            //DataTable dataStudentList = SqlHelper.GetDataTable(sql);

            if (cboSdept.SelectedIndex!=0)
            {
                sql += "and Sdept=@Sdept ";
            }
            SqlParameter[] paras =
            {
                new SqlParameter("Sdept",sdept),
                new SqlParameter("Sname","%"+sname+"%")
            };
            DataTable dataStudentList = SqlHelper.GetDataTable(sql, paras);
            dgvStudentList.DataSource = dataStudentList;
            //Init_colEdit_colDelete(dgvStudentList);
        }

        private void dgvStudentList_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex!=-1)
            {
                DataRow dr = (dgvStudentList.Rows[e.RowIndex].DataBoundItem as DataRowView).Row;

                DataGridViewCell cell = dgvStudentList.Rows[e.RowIndex].Cells[e.ColumnIndex];
                if (cell is DataGridViewLinkCell &&cell.FormattedValue.ToString() =="修改")
                {
                    //修改操作
                    string Sno = dr["Sno"].ToString().Trim();
                    FrmEditStudent frmEditStudent = new FrmEditStudent();
                    frmEditStudent.Tag = Sno;
                    frmEditStudent.MdiParent = this.MdiParent;
                    frmEditStudent.Show();//顶级窗体


                }
                else if(cell is DataGridViewLinkCell &&cell.FormattedValue.ToString()=="删除")
                {
                    //删除操作
                    if (MessageBox.Show("确定删除？", "删除学生提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question)==DialogResult.Yes) 
                    {                     
                        string sno = dr["Sno"].ToString();
                        //假删除 IsDeleted 0  1
                        string sqlDel = "update Student set IsDeleted=1 where Sno=@Sno ";
                        SqlParameter para = new SqlParameter("@Sno", sno);
                        int count=SqlHelper.ExecutNonQuery(sqlDel, para);

                        //SC表中的相关学生信息一同删除
                        string sqlDelSC = "update SC set IsDeleted=1 where Sno=@Sno";
                        SqlParameter paraDelSC = new SqlParameter("@Sno", sno);
                        SqlHelper.ExecuteScalar(sqlDelSC, paraDelSC);

                        if (count>0)
                        {
                            MessageBox.Show("删除成功","删除学生提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
                            //DataGridView数据并没有刷新，手动刷新
                            DataTable dtStudent = (DataTable)dgvStudentList.DataSource;
                            //dgvStudentList.DataSource = null;
                            dtStudent.Rows.Remove(dr);
                            dgvStudentList.DataSource = dtStudent;
                        }
                        else
                        {
                            MessageBox.Show("删除失败", "删除学生提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            return;
                        }
                     
                        //真删除 delete...where... 
                    }
                }
            }
        }

        public static void Init_colEdit_colDelete(DataGridView dataGridView)
        {
            // colEdit
            DataGridViewLinkColumn colEdit = new DataGridViewLinkColumn();
            colEdit.AutoSizeMode = System.Windows.Forms.DataGridViewAutoSizeColumnMode.AllCells;
            colEdit.DefaultCellStyle.NullValue = "修改";
            colEdit.HeaderText = "修改";
            colEdit.Name = "colEdit";
            colEdit.ReadOnly = true;
            colEdit.Resizable = System.Windows.Forms.DataGridViewTriState.True;
            colEdit.Width = 35;
            dataGridView.Columns.Add(colEdit);
            // 
            // colDelete
            DataGridViewLinkColumn colDelete = new DataGridViewLinkColumn();
            colDelete.AutoSizeMode = System.Windows.Forms.DataGridViewAutoSizeColumnMode.AllCells;
            colDelete.DefaultCellStyle.NullValue = "删除";
            colDelete.HeaderText = "删除";
            colDelete.Name = "colDelete";
            colDelete.ReadOnly = true;
            colDelete.Resizable = System.Windows.Forms.DataGridViewTriState.True;
            colDelete.Width = 35;
            dataGridView.Columns.Add(colDelete);
        }


    }
}
